package com.engine.modules.vector.mapper;

import com.alibaba.fastjson.JSONObject;
import com.engine.common.baseEntity.gis.Geojson.GeojsonEntity;
import com.engine.common.util.ListMapAndJson;
import com.engine.common.util.BasicParamUtil.StringUtils;
import com.engine.init.Globle;
import com.engine.modules.general.mapper.GeneralDataCommonMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.*;

@Repository
@Slf4j
public class VectorSourceMapper {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    public List<Map<String, Object>> selectGeojsonByIds(List<String> ids, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        List<Map<String, Object>> rsList = new ArrayList<>();
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        String general_columns = (String) Globle.TablePropertiesMap.get(tableName).getGeneralColumns();
        String sys_columns = (String) Globle.TablePropertiesMap.get(tableName).getSysColumns();
        String colums = general_columns + "," + sys_columns + ",ST_AsGeoJson(geom) as geojson";
        String condition = "";
        for (String str : ids) {
            if (StringUtils.checkUuidV2(str))
                condition += "'" + str + "',";
        }
        if (!condition.equals(""))
            condition = condition.substring(0, condition.length() - 1);
        String sql = "select " + colums +
                " from " + tableName +
                " where id IN (" + condition + ") AND del_flag='0' " + pacSql;
        log.debug(sql);
        rsList = jdbcTemplate.queryForList(sql);
        return rsList;
    }

    public List<Map<String, Object>> selectGeojsonByWhereStr(String tableName,String whereStr,  String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        List<Map<String, Object>> rsList = new ArrayList<>();
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        String general_columns = (String) Globle.TablePropertiesMap.get(tableName).getGeneralColumns();
        String sys_columns = (String) Globle.TablePropertiesMap.get(tableName).getSysColumns();
        String colums = general_columns + "," + sys_columns + ",ST_AsGeoJson(geom) as geojson";
        String sql = "select " + colums +
                " from " + tableName +
                " where (" + whereStr + ") AND del_flag='0' " + pacSql;
        log.debug(sql);
        rsList = jdbcTemplate.queryForList(sql);
        return rsList;
    }

    public List<Map<String, Object>> selectGeojsonByXY(double minX, double minY, double maxX, double maxY, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        if (minX == maxX)
            maxX += 0.00001;
        if (minY == maxY)
            maxY += 0.00001;
        String general_columns = (String) Globle.TablePropertiesMap.get(tableName).getGeneralColumns();
        String sys_columns = (String) Globle.TablePropertiesMap.get(tableName).getSysColumns();
        String colums = general_columns + "," + sys_columns + ",ST_AsGeoJson(geom) as geojson";
        String sql = "select " + colums +
                " from " + tableName +
                " where ST_Intersects(geom,ST_MakeEnvelope(" + minX + "," + minY + "," + maxX + "," + maxY + ",4326)) AND del_flag='0' " + pacSql;
        List<Map<String, Object>> rsList = jdbcTemplate.queryForList(sql);
        return rsList;
    }

    public List<Map<String, Object>> selectGeojsonByGeometry(String geometry, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        String general_columns = (String) Globle.TablePropertiesMap.get(tableName).getGeneralColumns();
        String sys_columns = (String) Globle.TablePropertiesMap.get(tableName).getSysColumns();
        String colums = general_columns + "," + sys_columns + ",ST_AsGeoJson(geom) as geojson";
        String sql = "select " + colums +
                " from " + tableName +
                " where ST_Intersects(geom, st_geomfromgeojson(?)) AND del_flag='0' " + pacSql;
        List<Map<String, Object>> rsList = jdbcTemplate.queryForList(sql, geometry);
        return rsList;
    }

    public Double selectShapeAreaByGeometry(String geometry) {
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("geometry", geometry);
        String sql = "SELECT st_area(ST_Transform(st_geomfromgeojson(:geometry),4521)) as shape_area";
        Map<String, Object> rsList = namedParameterJdbcTemplate.queryForMap(sql, mapArgs);
        return Double.parseDouble(rsList.get("shape_area").toString());
    }

    public String countGeojsonByGeometry(String geometry, String countColumns, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String whereSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            whereSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        String general_columns = (String) Globle.TablePropertiesMap.get(tableName).getGeneralColumns();
        String sys_columns = (String) Globle.TablePropertiesMap.get(tableName).getSysColumns();
        String colums = general_columns + "," + sys_columns + ",ST_AsGeoJson(geom) as geojson";
        String sql = "select " + colums +
                " from " + tableName +
                " where ST_Intersects(geom, st_geomfromgeojson(?)) AND del_flag='0' " + whereSql;
        List<Map<String, Object>> rsList = jdbcTemplate.queryForList(sql, geometry);
        String geojson = ListMapAndJson.ListMap2Geojson(rsList);
        sql = "select " + countColumns + " from " + tableName +
                " where ST_Intersects(geom, st_geomfromgeojson(?)) AND del_flag='0' " + whereSql;
        List<Map<String, Object>> countList = jdbcTemplate.queryForList(sql, geometry);
        geojson = ListMapAndJson.GeojsonAddMapInRoot(geojson, countList.get(0));
        return geojson;
    }

    public List<Map<String, Object>> selectMinNeighberGeojsonByGeometry(String geometry, Integer getCount, String tableName, String authCo) {
        String sql = "SELECT t.id FROM tob_gis_point_kfxx t ORDER BY t.geom <-> st_geomfromgeojson(?) LIMIT " + getCount;
        List<Map<String, Object>> rsList = jdbcTemplate.queryForList(sql, geometry);
        List<String> ids = new ArrayList<>();
        for (Map<String, Object> map : rsList)
            ids.add(map.get("id").toString());
        List<Map<String, Object>> rs = selectGeojsonByIds(ids, tableName, authCo);
        return rs;
    }


    public Integer deleteLogicByGeojson(String geojson, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        List<Map<String, Object>> listMap = ListMapAndJson.Geojson2ListMap(geojson);
        String ids = "";
        for (int i = 0; i < listMap.size(); i++) {
            if (i == listMap.size() - 1)
                ids += "'" + listMap.get(i).get("id").toString() + "'";
            else
                ids += "'" + listMap.get(i).get("id").toString() + "',";
        }
        String sql = "update " + tableName + " set del_flag = '1',gmt_update=NOW() where id in (" + ids + ") " + pacSql;
        return jdbcTemplate.update(sql);
    }

    public List<String> insertByGeojson(String geojson, String tableName) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        List<Map<String, Object>> listMap = ListMapAndJson.Geojson2ListMap(geojson);
        List<String> uuids = new ArrayList<>();
        for (int i = 0; i < listMap.size(); i++) {
            String uuid = ListMapAndJson.getUUID32();
            Set<String> set = listMap.get(i).keySet();            //得到由键组成的Set集合
            Iterator<String> it = set.iterator();
            String inputColums = "";
            String inputValue = "";
            List<Object> largs = new ArrayList<>();
            largs.add(listMap.get(i).get("geom"));
            while (it.hasNext()) {
                String key = it.next();
                if (key.equals("id") || key.equals("gmt_update") || key.equals("gmt_create") || key.equals("del_flag") || key.equals("geom"))
                    continue;
                inputColums += key + ",";
                inputValue += "?,";
                if (key.contains("time")) {
                    try {
                        largs.add(new Date((Long) listMap.get(i).get(key)));
                    } catch (Exception e) {
                        log.error(e.getMessage(), e);
                        largs.add(null);
                    }
                } else
                    largs.add(listMap.get(i).get(key));
            }
            inputColums += "id,";
            inputValue += "?,";
            largs.add(uuid);
            inputColums = inputColums.substring(0, inputColums.length() - 1);
            inputValue = inputValue.substring(0, inputValue.length() - 1);
            JSONObject js = JSONObject.parseObject(listMap.get(i).get("geom").toString());
            String gstr = "ST_GeomFromGeoJSON(?)";
            if (js.getString("type").contains("Polygon"))
                gstr = "ST_Multi(ST_GeomFromGeoJSON(?))";
            // 2021年7月29日 由于插入数据存在未闭合与自相交错误，故在入库之前做数据校验
            String IsValidGeom = "SELECT st_IsValidReason(st_geomfromgeojson(?)) as result limit 1";
            Map<String,Object> rs=jdbcTemplate.queryForMap(IsValidGeom,listMap.get(i).get("geom"));
            String str=(String)rs.get("result");
            // 只有当geom合法时才插入
            if (str.equals("Valid Geometry")) {
                String sql =
                        "insert into " + tableName + " (gmt_update,gmt_create,del_flag,geom," + inputColums + ") " +
                                "values (NOW(), NOW(), 0," + gstr + "," + inputValue + ")";
                log.debug(sql);
                int flag = jdbcTemplate.update(sql, largs.toArray());
                if (flag == 1)
                    uuids.add(uuid);
            } else if (str.contains("IllegalArgumentException") || str.contains("Self-intersection")) {
                // 如果geom经过校验出现IllegalArgumentException或者Self-intersection错误则返回该数据的uuid与出错原因
                uuids.add(uuid + " 因为 " + str+" (未闭合或者自相交)插入失败，请检查数据格式！");
            }
        }
        return uuids;
    }
    public String insertByGeojsonEntity(GeojsonEntity geojsonEntity, String tableName) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");

        return "";
    }

    public List<String> insertByGeojsonUseOwnId(String geojson, String tableName) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        List<Map<String, Object>> listMap = ListMapAndJson.Geojson2ListMap(geojson);
        // listMap中也有id
        List<String> uuids = new ArrayList<>();
        for (int i = 0; i < listMap.size(); i++) {
            // 从map中取出id的值
            String uuid = listMap.get(i).get("id").toString();
            Set<String> set = listMap.get(i).keySet();            //得到由键组成的Set集合
            Iterator<String> it = set.iterator();
            String inputColums = "";
            String inputValue = "";
            List<Object> largs = new ArrayList<>();
            largs.add(listMap.get(i).get("geom"));
            while (it.hasNext()) {
                String key = it.next();
                if (key.equals("id") || key.equals("gmt_update") || key.equals("gmt_create") || key.equals("del_flag") || key.equals("geom"))
                    continue;
                inputColums += key + ",";
                inputValue += "?,";
                if (key.contains("time")) {
                    try {
                        largs.add(new Date((Long) listMap.get(i).get(key)));
                    } catch (Exception e) {
                        log.error(e.getMessage(), e);
                        largs.add(null);
                    }
                } else
                    largs.add(listMap.get(i).get(key));
            }
            inputColums += "id,";
            inputValue += "?,";
            largs.add(uuid);
            inputColums = inputColums.substring(0, inputColums.length() - 1);
            inputValue = inputValue.substring(0, inputValue.length() - 1);
            JSONObject js = JSONObject.parseObject(listMap.get(i).get("geom").toString());
            String gstr = "ST_GeomFromGeoJSON(?)";
            if (js.getString("type").contains("Polygon"))
                gstr = "ST_Multi(ST_GeomFromGeoJSON(?))";
            String sql =
                    "insert into " + tableName + " (gmt_update,gmt_create,del_flag,geom," + inputColums + ") " +
                            "values (to_char(NOW(),'yyyyMMdd HH24:mm:dd'),to_char(NOW(),'yyyyMMdd HH24:mm:dd'), 0," + gstr + "," + inputValue + ")";
            System.out.println(sql);
            log.info(sql);
            int flag = jdbcTemplate.update(sql, largs.toArray());
            if (flag == 1)
                uuids.add(uuid);
        }
        return uuids;
    }


    public List<String> updateByGeojson(String geojson, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        List<Map<String, Object>> listMap = ListMapAndJson.Geojson2ListMap(geojson);
        int count = 0;
        List<String> uuids=new ArrayList<>();
        for (int i = 0; i < listMap.size(); i++) {
            Set<String> set = listMap.get(i).keySet();            //得到由键组成的Set集合
            Iterator<String> it = set.iterator();
            String optSeq = "";
            List<Object> lars = new ArrayList<>();
            // 2021年7月29日 由于插入数据存在未闭合与自相交错误，故在入库之前做数据校验
            String IsValidGeom = "SELECT st_IsValidReason(st_geomfromgeojson(?)) as result limit 1";
            Map<String,Object> rs=jdbcTemplate.queryForMap(IsValidGeom,listMap.get(i).get("geom"));
            String str=(String)rs.get("result");
            if(listMap.get(i).get("id")==null)
            {
                uuids.add("数据缺失id，无法更新");
                continue;
            }
            // 只有当geom合法时才插入
            if (str.equals("Valid Geometry")) {
                lars.add(listMap.get(i).get("geom"));
                while (it.hasNext()) {
                    String key = it.next();
                    if (key.equals("id") || key.equals("gmt_update") || key.equals("gmt_create") || key.equals("del_flag") || key.equals("geom"))
                        continue;
                    if (key.contains("time")) {
                        try {
                            lars.add(new Date((Long) listMap.get(i).get(key)));
                        } catch (Exception e) {
                            log.error(e.getMessage(), e);
                            lars.add(null);
                        }
                        optSeq += key + "=?,";
                        continue;
                    }
                    optSeq += key + "=?,";
                    lars.add(listMap.get(i).get(key));
                }
                optSeq = optSeq.substring(0, optSeq.length() - 1);
                log.info(optSeq);
                String sql = "update " + tableName +
                        " set gmt_update=NOW()," +
                        "del_flag='0'," +
                        "geom=ST_GeomFromGeoJSON(?)," +
                        optSeq +
                        " where id = '" + listMap.get(i).get("id").toString() + "' " + pacSql;
                count += jdbcTemplate.update(sql, lars.toArray());
                uuids.add(listMap.get(i).get("id").toString());
                log.info(sql);
                log.info(Arrays.toString(lars.toArray()));
            } else if (str.contains("IllegalArgumentException") || str.contains("Self-intersection")) {
                // 如果geom经过校验出现IllegalArgumentException或者Self-intersection错误则返回该数据的uuid与出错原因
                uuids.add(listMap.get(i).get("id").toString() + " 因为 " + str+" (未闭合或者自相交)插入失败，请检查数据格式！");
            }
        }
        return uuids;
    }

    public List<String> deleteLogicByIds(List<String> ids, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        String idl = "";
        List<String> rs=new ArrayList<>();
        for (int i = 0; i < ids.size(); i++) {
            String sql = "update " + tableName + " set del_flag = '1',gmt_update=NOW() where id=?" + pacSql;
            int r=jdbcTemplate.update(sql,ids.get(i));
            if(r>0)
                rs.add(ids.get(i));
        }
        return rs;
    }

    public List<String> deleteByIds(List<String> ids, String tableName, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String pacSql = "";
        if (Globle.TablePropertiesMap.get(tableName).getPermissionControl())
            pacSql += " AND (LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        List<String> rs=new ArrayList<>();
        for (int i = 0; i < ids.size(); i++) {
            String sql = "DELETE FROM " + tableName + " WHERE id = ? "+ pacSql;
            log.debug(sql);
            int x=jdbcTemplate.update(sql,ids.get(i));
            if(x==1)
                rs.add(ids.get(i));
        }
        return rs;
    }
}
